﻿Imports System.Data.Linq
Imports System.Linq

Public Class MStatisticsPriceFile
    Private db As New TanNamChinhDataContext
    Public Function getFileFeeList() As DataTable

        'Dim groups = From a In db.tblFileFees
        '                   Group By IDFileFee = a.ID_FileFee, IDFee = a.ID_Fee, FeeName = a.tblFee.FeeName, Price = a.Price
        '                   Into myGroup = Group, Count()
        '                    Select IDFileFee,
        '                    IDFee,
        '                    FeeName, Price
        Dim dt As New DataTable
        Try
            Dim groups = From a In db.tblFileFees
                       Group By IDFile = a.ID_File, FileNo = a.tblFile.FileNo, CustomerName = a.tblFile.tblCustomer.CustomerName
                       Into myGroup = Group, Count()
                        Select IDFile, FileNo, CustomerName

            Dim listFeeBuying = (From d In db.tblFees
                                 Where d.ID_FeeType = 1
                           Select d.FeeName).Distinct()
            Dim listFeeSelling = (From d In db.tblFees
                                  Where d.ID_FeeType = 2
                          Select d.FeeName).Distinct()



            dt.Columns.Add("ID_File")
            dt.Columns.Add("FileNo")
            dt.Columns.Add("CustomerName")
            dt.Columns.Add("Tigia")
            For Each obj In listFeeBuying
                dt.Columns.Add(obj.ToString() + "_B")
            Next
            dt.Columns.Add("TotalBuying")
            For Each obj In listFeeSelling
                dt.Columns.Add(obj.ToString() + "_S")
            Next
            dt.Columns.Add("TotalSelling")
            For Each g In groups
                Dim dr As DataRow = dt.NewRow()
                dr("ID_File") = g.IDFile
                dr("FileNo") = g.FileNo
                dr("CustomerName") = g.CustomerName
                Dim mf As New MFiles
                Dim tigia = mf.GetRatioCurrency(g.IDFile)
                dr("Tigia") = tigia
                Dim sum = 0
                Dim listDetailFeeBuying = (From d In db.tblFileFees
                                     Where d.ID_File = g.IDFile
                      Select d.ID_Fee, d.tblFee.FeeName, d.Price)
                'Lấy tỉ giá của file

                For Each pr In listDetailFeeBuying

                    If pr.Price IsNot Nothing Then
                        dr(pr.FeeName + "_B") = CDbl(pr.Price)
                        sum += CDbl(pr.Price)
                    Else
                        dr(pr.FeeName + "_B") = CDbl(0)
                        sum += CDbl(0)
                    End If

                Next
                dr("TotalBuying") = sum
                sum = 0
                Try
                    Dim listDetailFeeSelling = (From d In db.tblSellingFees
                                    Where d.tblHBL.tblFileMBL.ID_File = g.IDFile
                     Select d.ID_Fee, d.tblFee.FeeName, d.Price)
                    For Each pr In listDetailFeeSelling

                        If pr.Price IsNot Nothing Then
                            dr(pr.FeeName + "_S") = CDbl(pr.Price)
                            sum += CDbl(pr.Price)
                        Else
                            dr(pr.FeeName + "_S") = 0
                            sum += CDbl(0)
                        End If

                       
                    Next
                    dr("TotalSelling") = sum
                Catch ex As Exception

                End Try

                dt.Rows.Add(dr)
            Next
        Catch ex As Exception
            Throw
        End Try
     
        Return dt
    End Function
    ''' <summary>
    ''' </summary>
    ''' <param name="IsExport"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getFileFeeList(ByVal IsExport As Boolean) As DataTable

        'Dim groups = From a In db.tblFileFees
        '                   Group By IDFileFee = a.ID_FileFee, IDFee = a.ID_Fee, FeeName = a.tblFee.FeeName, Price = a.Price
        '                   Into myGroup = Group, Count()
        '                    Select IDFileFee,
        '                    IDFee,
        '                    FeeName, Price
        Dim dt As New DataTable
        Try

            If IsExport Then
                'Hàng xuất
                Dim groups = From a In db.tblFileFees
                             Where a.tblFile.IsExport = True
                    Group By IDFile = a.ID_File, FileNo = a.tblFile.FileNo, CustomerName = a.tblFile.tblCustomer.CustomerName
                    Into myGroup = Group, Count()
                     Select IDFile, FileNo, CustomerName

                Dim listFeeBuying = (From d In db.tblFees
                                     Where d.ID_FeeType = 1
                               Select d.FeeName).Distinct()
                Dim listFeeSelling = (From d In db.tblFees
                                      Where d.ID_FeeType = 2
                              Select d.FeeName).Distinct()



                dt.Columns.Add("ID_File")
                dt.Columns.Add("FileNo")
                dt.Columns.Add("CustomerName")
                dt.Columns.Add("Tigia")
                For Each obj In listFeeBuying
                    dt.Columns.Add(obj.ToString() + "_B")
                Next
                dt.Columns.Add("TotalBuying")
                For Each obj In listFeeSelling
                    dt.Columns.Add(obj.ToString() + "_S")
                Next
                dt.Columns.Add("TotalSelling")
                For Each g In groups
                    Dim dr As DataRow = dt.NewRow()
                    dr("ID_File") = g.IDFile
                    dr("FileNo") = g.FileNo
                    dr("CustomerName") = g.CustomerName
                    Dim mf As New MFiles
                    Dim tigia = mf.GetRatioCurrency(g.IDFile)
                    dr("Tigia") = tigia
                    Dim sum = 0
                    Dim listDetailFeeBuying = (From d In db.tblFileFees
                                         Where d.ID_File = g.IDFile And d.tblFile.IsExport = True
                          Select d.ID_Fee, d.tblFee.FeeName, d.Price)
                    'Lấy tỉ giá của file

                    For Each pr In listDetailFeeBuying

                        If pr.Price IsNot Nothing Then
                            dr(pr.FeeName + "_B") = CDbl(pr.Price)
                            sum += CDbl(pr.Price)
                        Else
                            dr(pr.FeeName + "_B") = CDbl(0)
                            sum += CDbl(0)
                        End If

                    Next
                    dr("TotalBuying") = sum
                    sum = 0
                    Try
                        Dim listDetailFeeSelling = (From d In db.tblSellingFees
                                        Where d.tblHBL.tblFileMBL.ID_File = g.IDFile And d.tblHBL.tblFileMBL.tblFile.IsExport = True
                         Select d.ID_Fee, d.tblFee.FeeName, d.Price)
                        For Each pr In listDetailFeeSelling

                            If pr.Price IsNot Nothing Then
                                dr(pr.FeeName + "_S") = CDbl(pr.Price)
                                sum += CDbl(pr.Price)
                            Else
                                dr(pr.FeeName + "_S") = 0
                                sum += CDbl(0)
                            End If


                        Next
                        dr("TotalSelling") = sum
                    Catch ex As Exception

                    End Try

                    dt.Rows.Add(dr)
                Next
            Else
                'Hàng nhập 
                Dim groups = From a In db.tblFileFees
                            Where a.tblFile.IsExport = False
                   Group By IDFile = a.ID_File, FileNo = a.tblFile.FileNo, CustomerName = a.tblFile.tblCustomer.CustomerName
                   Into myGroup = Group, Count()
                    Select IDFile, FileNo, CustomerName

                Dim listFeeBuying = (From d In db.tblFees
                                     Where d.ID_FeeType = 1
                               Select d.FeeName).Distinct()
                Dim listFeeSelling = (From d In db.tblFees
                                      Where d.ID_FeeType = 2
                              Select d.FeeName).Distinct()



                dt.Columns.Add("ID_File")
                dt.Columns.Add("FileNo")
                dt.Columns.Add("CustomerName")
                dt.Columns.Add("Tigia")
                For Each obj In listFeeBuying
                    dt.Columns.Add(obj.ToString() + "_B")
                Next
                dt.Columns.Add("TotalBuying")
                For Each obj In listFeeSelling
                    dt.Columns.Add(obj.ToString() + "_S")
                Next
                dt.Columns.Add("TotalSelling")
                For Each g In groups
                    Dim dr As DataRow = dt.NewRow()
                    dr("ID_File") = g.IDFile
                    dr("FileNo") = g.FileNo
                    dr("CustomerName") = g.CustomerName
                    Dim mf As New MFiles
                    Dim tigia = mf.GetRatioCurrency(g.IDFile)
                    dr("Tigia") = tigia
                    Dim sum = 0
                    Dim listDetailFeeBuying = (From d In db.tblFileFees
                                         Where d.ID_File = g.IDFile And d.tblFile.IsExport = False
                          Select d.ID_Fee, d.tblFee.FeeName, d.Price)
                    'Lấy tỉ giá của file

                    For Each pr In listDetailFeeBuying

                        If pr.Price IsNot Nothing Then
                            dr(pr.FeeName + "_B") = CDbl(pr.Price)
                            sum += CDbl(pr.Price)
                        Else
                            dr(pr.FeeName + "_B") = CDbl(0)
                            sum += CDbl(0)
                        End If

                    Next
                    dr("TotalBuying") = sum
                    sum = 0
                    Try
                        Dim listDetailFeeSelling = (From d In db.tblSellingFees
                                        Where d.tblHBL.tblFileMBL.ID_File = g.IDFile And d.tblHBL.tblFileMBL.tblFile.IsExport = False
                         Select d.ID_Fee, d.tblFee.FeeName, d.Price)
                        For Each pr In listDetailFeeSelling

                            If pr.Price IsNot Nothing Then
                                dr(pr.FeeName + "_S") = CDbl(pr.Price)
                                sum += CDbl(pr.Price)
                            Else
                                dr(pr.FeeName + "_S") = 0
                                sum += CDbl(0)
                            End If


                        Next
                        dr("TotalSelling") = sum
                    Catch ex As Exception

                    End Try

                    dt.Rows.Add(dr)
                Next
            End If

         
        Catch ex As Exception
            Throw
        End Try

        Return dt
    End Function
    
End Class
